package cn.cmcc.online.smsapi.sdk.db.entity;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import cn.cmcc.online.smsapi.sdk.constant.Constant;
import cn.cmcc.online.smsapi.sdk.db.DBManager;
import cn.cmcc.online.smsapi.sdk.db.XyCursor;
import cn.cmcc.online.smsapi.sdk.iccid.IccidLocationUtil;
import cn.cmcc.online.smsapi.sdk.log.LogManager;
import cn.cmcc.online.smsapi.sdk.util.StringUtils;

 
/**
 * iccid管理
 * @author Administrator
 */
public class IccidInfoManager {
	public final static String ID="id";
	 
	public final static String ICCID ="iccid";//iccid
	public final static String CITY="city";//城市
	public final static String PROVINCES="provinces";//省份
	public final static String OPERATOR="operator";//运营商
	public final static String AREACODE="areacode";//归属地简称
	public final static String UPDATE_TIME="updateTime";//更新到服务器的时间
	
	public final static String ISPOST = "ispost";//是否已经提交服务器0未提交，1已提交
	public final static String NUM = "num";//接收号码10086,10010,10000
	public final static String CNUM = "cnum";//中心号码;
	public final static String DEFT = "deft";//是否默认 1:是 0:不是
	public final static String NET_UPDATE_TIME="net_updateTime";//数据网络更新数据的时间

	public final static String USER_PROVINCES="user_provinces";//用户手动设置的省份
	public final static String USER_AREACODE="user_areacode";//用户手动设置的省份编码
	public final static String USER_OPERATOR="user_operator";//用户手动设置运营商
	public final static String SIM_INDEX="sim_index";//所属sim卡位。 0:卡1, 1:卡2 -1:无效
	
	public static final String TABLE_NAME="tb_phone_info";
	public static final String DROP_TABLE = " DROP TABLE IF EXISTS " + TABLE_NAME;
	public static final String CREATE_TABLE = "create table  if not exists "  + TABLE_NAME + " ("
											+ID+" INTEGER PRIMARY KEY,"
											+ICCID+" TEXT ,"
											+CITY+" TEXT,"
											+PROVINCES+" TEXT,"
											+OPERATOR+" TEXT,"	
										    +AREACODE+" TEXT,"	
											+ ISPOST+" INTEGER DEFAULT 0,"
											+NUM+" TEXT,"
											+CNUM+" TEXT,"
											+UPDATE_TIME+" LONG,"	
											+DEFT+"  INTEGER DEFAULT 0,"
											+NET_UPDATE_TIME+" LONG DEFAULT 0,"
											+USER_PROVINCES+" TEXT,"
											+USER_AREACODE+" TEXT,"
											+USER_OPERATOR+" TEXT,"
											+SIM_INDEX+" INTEGER DEFAULT -1)";
	
	
	public static final String ADD_USER_PROVINCES = "ALTER TABLE "+ TABLE_NAME +" ADD COLUMN "+USER_PROVINCES+" TEXT ";
	public static final String ADD_USER_AREACODE = "ALTER TABLE "+ TABLE_NAME +" ADD COLUMN "+USER_AREACODE+" TEXT ";
	public static final String ADD_USER_OPERATOR = "ALTER TABLE "+ TABLE_NAME +" ADD COLUMN "+USER_OPERATOR+" TEXT ";
	public static final String ADD_SIM_INDEX = "ALTER TABLE "+ TABLE_NAME +" ADD COLUMN "+SIM_INDEX+" INTEGER DEFAULT -1 ";
	
	/**
	 * 查询默认的IccidInfo
	 * @param ctx
	 * @return
	 */
	 
	public static IccidInfo queryDeftIccidInfo(Context ctx){
		return queryIccidInfo(null,ctx);
	 }
	/**
	 * 查询iccid信息
	 * @param iccid
	 * @param ctx
	 * @return
	 */
	public static IccidInfo queryIccidInfo(String iccid,Context ctx){
        return queryIccidInfo(iccid, -1);
	} 
	
    /**
     * 更新或新增用户设置的sim卡信息
     * 
     * @param iccid
     *            sim卡iccid
     * @param customSimIndex
     *            sim卡位
     * @param userProvinces
     *            用户设置的省份信息
     * @param userAreacode
     *            用户设置的区域编码信息
     * @param userOperator
     *            用户设置的运营商信息
     * @param deft
     *            用户设置的默认sim卡
     * @return
     */
    public static boolean insertOrUpdateIccid(String iccid, int customSimIndex, String userProvinces,
            String userAreacode, String userOperator, boolean deft) {
        if (customSimIndex < 0 || StringUtils.isNull(userProvinces) || StringUtils.isNull(userAreacode)
                || StringUtils.isNull(userOperator)) {
            return false;
        }

        // boolean result = false;
        SQLiteDatabase db = null;
        try {
            db = DBManager.getSQLiteDatabase();
            db.beginTransaction();

            // 更新或插入用户设置的sim卡信息失败,返回false并回滚数据库操作
            if (insertOrUpdateIccid(db, iccid, customSimIndex, userProvinces, userAreacode, userOperator, deft) < 1) {
                return false;
            }

            db.setTransactionSuccessful();
            // result = true;
            return true;
        } catch (Throwable e) {
            // e.printStackTrace();
            /* SDK-550 hewengao 20160719 start */
            LogManager.w(Constant.TAG, "insertOrUpdateIccid: ", e);
            /* SDK-550 hewengao 20160719 end */
            return false;
        } finally {
            if (db != null) {
                try {
                    if (db.inTransaction()) {
                        db.endTransaction();
                    }
                } catch (Throwable e) {
                    // e.printStackTrace();
                    /* SDK-550 hewengao 20160719 start */
                    LogManager.w(Constant.TAG, "insertOrUpdateIccid: ", e);
                    /* SDK-550 hewengao 20160719 end */
                } finally {
                    DBManager.close(db);
                }
            }
        }
    }
 
	/**
	 * 根据iccid及sim卡位获取iccid信息
	 * 
	 * @param iccid
	 * @param customSimIndex
	 * @return
	 */
    public static IccidInfo queryIccidInfo(String iccid, int customSimIndex) {
        XyCursor cursor = null;
        IccidInfo iccidInfo = null;
        try {
            String sqlWhere = null;
            if (!StringUtils.isNull(iccid)) {
                sqlWhere = ICCID + "='" + iccid + "'";
            } else if (customSimIndex >= 0) {
                /* SDK-485 huangzhiqiang 20160621 start */
                sqlWhere = "(" + ICCID + " IS NULL OR " + ICCID + "='' ) AND " + SIM_INDEX + "='" + customSimIndex + "'";
                /* SDK-485 huangzhiqiang 20160621 end */
            } else {
                sqlWhere = DEFT + "=1";
            }
            /* SDK-450 huangzhiqiang 20160624 start*/
            cursor = DBManager.query(
                    TABLE_NAME, new String[] { ID, CITY, PROVINCES, UPDATE_TIME, OPERATOR, ISPOST, NUM, CNUM,
                            NET_UPDATE_TIME, AREACODE, ICCID, DEFT, USER_PROVINCES, USER_AREACODE, USER_OPERATOR, SIM_INDEX },
                    sqlWhere, null);
            /* SDK-450 huangzhiqiang 20160624 end*/
            if (cursor != null && cursor.moveToNext()) {
                iccidInfo = new IccidInfo();
                /* SDK-485 huangzhiqiang 20160621 start */
                iccidInfo.id = cursor.getInt(cursor.getColumnIndex(ID));
                /* SDK-485 huangzhiqiang 20160621 end */
                iccidInfo.city = cursor.getString(cursor.getColumnIndex(CITY));
                iccidInfo.operator = cursor.getString(cursor.getColumnIndex(OPERATOR));
                iccidInfo.areaCode = cursor.getString(cursor.getColumnIndex(AREACODE));
                iccidInfo.provinces = cursor.getString(cursor.getColumnIndex(PROVINCES));
                iccidInfo.updateTime = cursor.getLong(cursor.getColumnIndex(UPDATE_TIME));
                iccidInfo.iccid = cursor.getString(cursor.getColumnIndex(ICCID));
                iccidInfo.isPost = cursor.getInt(cursor.getColumnIndex(ISPOST));
                iccidInfo.num = cursor.getString(cursor.getColumnIndex(NUM));
                iccidInfo.cnum = cursor.getString(cursor.getColumnIndex(CNUM));
                iccidInfo.netUpdateTime = cursor.getLong(cursor.getColumnIndex(NET_UPDATE_TIME));
                /* SDK-450 huangzhiqiang 20160624 start*/
                iccidInfo.deft = cursor.getInt(cursor.getColumnIndex(DEFT));
                /* SDK-450 huangzhiqiang 20160624 end*/
                iccidInfo.userProvinces = cursor.getString(cursor.getColumnIndex(USER_PROVINCES));
                iccidInfo.userAreacode = cursor.getString(cursor.getColumnIndex(USER_AREACODE));
                iccidInfo.userOperator = cursor.getString(cursor.getColumnIndex(USER_OPERATOR));
                iccidInfo.simIndex = cursor.getInt(cursor.getColumnIndex(SIM_INDEX));
            }
        } catch (Throwable e) {
//            e.printStackTrace();
            /*SDK-550 hewengao 20160719 start*/
            LogManager.w(Constant.TAG, "queryIccidInfo: ", e);
            /*SDK-550 hewengao 20160719 end*/
        } finally {
            XyCursor.closeCursor(cursor, true);
        }
        return iccidInfo;
    }

    /**
     * 更新或新增iccid信处
     * 
     * @param info
     * @param deft
     * @param c tx
     * @return
     */
    public static long insertIccid(String simIccid, boolean deft, String provice, String areacode, String city,
            String operator, Context ctx) {
        long flag = -1;
        try {
            ContentValues values = new ContentValues();
            values.put(ICCID, simIccid);

            if (!StringUtils.isNull(provice) && provice.indexOf(";") == -1) {
                if (!StringUtils.isNull(provice)) {
                    values.put(PROVINCES, provice.trim());
                    values.put(UPDATE_TIME, System.currentTimeMillis());
                }
                if (!StringUtils.isNull(areacode)) {
                    values.put(AREACODE, areacode.trim());
                } else {
                    values.put(AREACODE, getProviceCode(provice));
                }
                if (!StringUtils.isNull(city)) {
                    values.put(CITY, city.trim());
                }
                if (!StringUtils.isNull(operator)) {
                    values.put(OPERATOR, operator);
                }
            } else {
                String[] locate = null;
                if (!StringUtils.isNull(provice)) {
                    locate = provice.split(";");
                    if (locate.length >= 1) {
                        values.put(PROVINCES, locate[0]);
                        values.put(UPDATE_TIME, System.currentTimeMillis());
                        values.put(AREACODE, getProviceCode(locate[0]));
                    }
                    if (locate.length >= 2) {
                        values.put(CITY, locate[1]);
                    }
                }

            }
            if (deft) {
                ContentValues updateDeft = new ContentValues();
                updateDeft.put(DEFT, 0);
                DBManager.update(TABLE_NAME, updateDeft, null, null);
            }
            values.put(DEFT, deft ? 1 : 0);
            flag = DBManager.update(TABLE_NAME, values, ICCID + " = ?", new String[] { simIccid });
            if (flag < 1) {
                /* SDK-450 huangzhiqiang 20160624 start */
                values.put(SIM_INDEX, -1); // 默认是未知卡位
                /* SDK-450 huangzhiqiang 20160624 end */
                flag = DBManager.insert(TABLE_NAME, values);
                /* SDK-450 huangzhiqiang 20160624 start */
                // 新增数据缓存
                IccidLocationUtil.putIccidAreaCodeToCache(simIccid, values.getAsString(AREACODE), operator, null, null,
                        -1, 0);
                /* SDK-450 huangzhiqiang 20160624 end */
            } else {
                IccidInfo iccidData = IccidInfoManager.queryIccidInfo(simIccid, Constant.getContext());
                if (iccidData != null) {
                    /* SDK-450 huangzhiqiang 20160624 start */
                    // 更新数据缓存
                    IccidLocationUtil.putIccidAreaCodeToCache(simIccid, iccidData.areaCode, iccidData.operator,
                            iccidData.userAreacode, iccidData.userOperator, iccidData.simIndex, iccidData.deft);
                    /* SDK-450 huangzhiqiang 20160624 end */
                }
            }
        } catch (Throwable e) {
            /*SDK-550 hewengao 20160719 start*/
            LogManager.w(Constant.TAG, "insertIccid: ", e);
            /*SDK-550 hewengao 20160719 end*/
        }
        return flag;
    }

    /**
     * 更新或新增用户设置的sim卡信息
     * 
     * @param db
     * @param simIccid
     *            sim卡iccid
     * @param simIndex
     *            sim卡位(-1:未知 0:卡1 1:卡2...)
     * @param userProvinces
     *            用户设置的省份
     * @param userAreacode
     *            用户设置的区域编码
     * @param userOperator
     *            用户设置的运营商
     * @param deft
     *            用户设置的默认sim卡
     * @return
     */
    private static long insertOrUpdateIccid(SQLiteDatabase db, String simIccid, int simIndex, String userProvinces,
            String userAreacode, String userOperator, boolean deft) {
        try {
            long flag = -1;
            ContentValues values = new ContentValues();
            values.put(ICCID, simIccid);
            values.put(SIM_INDEX, simIndex);

            if (!StringUtils.isNull(userProvinces)) {
                values.put(USER_PROVINCES, userProvinces.trim());
                values.put(UPDATE_TIME, System.currentTimeMillis());
            }
            if (!StringUtils.isNull(userAreacode)) {
                values.put(USER_AREACODE, userAreacode.trim());
            } else {
                values.put(USER_AREACODE, getProviceCode(userProvinces));
            }
            if (!StringUtils.isNull(userOperator)) {
                values.put(USER_OPERATOR, userOperator);
            }
            // if (deft) {
            // ContentValues updateDeft = new ContentValues();
            // updateDeft.put(DEFT, 0);
            // db.update(TABLE_NAME, updateDeft, null, null);
            // }
            // values.put(DEFT, deft ? 1 : 0);

            if (StringUtils.isNull(simIccid)) {
                /* SDK-485 huangzhiqiang 20160621 start */
                flag = db.update(TABLE_NAME, values, SIM_INDEX + " = " + simIndex + " AND (" + ICCID + " IS NULL OR "
                        + ICCID + "='' )", null);
                /* SDK-485 huangzhiqiang 20160621 end */
            } else {
                flag = db.update(TABLE_NAME, values, ICCID + " = ?", new String[] { simIccid });
                if (flag < 1) {
                    // 更新失败,需要插入新的卡位信息,将iccid不为空并且占用了这个卡位的数据的卡位改为-1
                    db.execSQL("UPDATE " + TABLE_NAME + " SET " + SIM_INDEX + "= -1 WHERE " + SIM_INDEX + "="
                            + simIndex + " AND " + ICCID + " IS NOT NULL");
                }
            }
            if (flag < 1) {
                flag = db.insert(TABLE_NAME, null, values);
            }
            return flag;
        } catch (Throwable e) {
            /* SDK-550 hewengao 20160719 start */
            LogManager.w(Constant.TAG, "insertOrUpdateIccid: ", e);
            /* SDK-550 hewengao 20160719 end */
            return -1;
        }
    }
 
    /**
     * 删除用户设置的sim卡信息
     * 
     * @param iccid
     * @param customSimIndex
     * @return 返回受影响行数,返回-1代表更新出现异常
     */
    public static int deleteIccidInfo(String iccid, int customSimIndex) {
        String sqlWhere = null;
        if (iccid != null && iccid.length() > 0) {
            sqlWhere = ICCID + "='" + iccid + "'";
        } else {
            /* SDK-485 huangzhiqiang 20160621 start */
            sqlWhere = "(" + ICCID + " IS NULL OR " + ICCID + "='' ) AND " + SIM_INDEX + "='" + customSimIndex + "'";
            /* SDK-485 huangzhiqiang 20160621 end */
        }
        try {
            ContentValues values = new ContentValues();
            values.put(USER_PROVINCES, "");
            values.put(USER_AREACODE, "");
            values.put(USER_OPERATOR, "");
            return DBManager.update(TABLE_NAME, values, sqlWhere, null);
        } catch (Throwable e) {
            // e.printStackTrace();
            /* SDK-550 hewengao 20160719 start */
            LogManager.w(Constant.TAG, "deleteIccidInfo: ", e);
            /* SDK-550 hewengao 20160719 end */
            return -1;
        }
    }

    /**
     * 更新iccid短信中心号码
     * 
     * @param iccid
     * @param cnum
     * @param num
     * @param ctx
     */
    public static void updateIccidCnum(String iccid, String cnum, String num, Context ctx) {
        try {
            if (!StringUtils.isNull(iccid) && !StringUtils.isNull(cnum) && !StringUtils.isNull(num)
                    && (num.equals("10086") || num.equals("10010") || num.equals("10000"))) {
                ContentValues values = new ContentValues();
                values.put(NUM, num);
                values.put(CNUM, cnum);
                DBManager.update(TABLE_NAME, values, ICCID + " = ? and " + ISPOST + " = 0", new String[] { iccid });
            }
        } catch (Throwable e) {
            // e.printStackTrace();
            /* SDK-550 hewengao 20160719 start */
            LogManager.w(Constant.TAG, "updateIccidCnum: ", e);
            /* SDK-550 hewengao 20160719 end */
        }
    }
	public static String getHead(IccidInfo iccidInfo){
		if(iccidInfo!=null&&iccidInfo.isPost==0&&
				!StringUtils.isNull(iccidInfo.iccid)
				&&!StringUtils.isNull(iccidInfo.num)
				&&!StringUtils.isNull(iccidInfo.cnum))
		{
			return iccidInfo.num+";"+iccidInfo.cnum+
					";"+iccidInfo.iccid;
		}
		return "";
	}
	/**
	 * 获取省份编码
	 * @param provice 省份
	 * @return 省份编码,自定义省份编码
	 */
	public static String getProviceCode(String p){
		String [][] code=new String[][]{
				{"北京","BJ"},{"上海","SH"},{"天津","TJ"},{"重庆","CQ"},{"黑龙江","HL"},{"吉林","JL"},{"辽宁","LN"},
				{"新疆","XJ"},{"西藏","XZ"},{"内蒙古","NM"},{"甘肃","GS"},{"青海","QH"},{"陕西","XA"},{"宁夏","NX"},{"山西","SX"},{"山东","SD"},
				{"安徽","AW"},{"河南","HN"},{"河北","HB"},{"浙江","ZJ"},{"江苏","JS"},{"湖南","CS"},{"湖北","WH"},{"贵州","GZ"},{"四川","SC"},
				{"江西","JX"},{"云南","YN"},{"广东","GD"},{"广西","GX"},{"福建","FJ"},{"海南","HK"},
				{"香港","XG"},{"澳门","OM"},{"台湾","TW"}
				 	
		};
        int len = code.length;
		for(int i = 0 ;i < len;i++){
			if(index(p,code[i][0])){
				return code[i][1];
			} 
		}
        return null;
	}
	static boolean index(String pro,String sub){
		if(pro != null && sub != null && pro.indexOf(sub) != -1){
			return true;
		}
		return false;
	}
}
